package rest.home.dao.dao;

import rest.home.coontuil.ConnUtil;
import rest.home.dao.RestHomeD;
import rest.home.entity.Admin;
import rest.home.entity.Health;
import rest.home.entity.Room;
import rest.home.entity.Trouble;
import rest.home.entity.*;
import rest.home.entity.ms.OlderAndVisitorAndRoom;

import java.net.CookieHandler;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: lbc
 * @Date: 2022/5/5 15:28
 * @version: 8.0
 * @describe:
 */
public class RestHomeDao implements RestHomeD {

    //登录
    public List<Admin> login() throws SQLException {
        ArrayList<Admin> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM users");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Admin admin = new Admin();
            admin.setAdminId(rs.getString("admin_id"));
            admin.setPassWord(rs.getString("password"));
            list.add(admin);
        }
        return list;
    }

    //新增账号密码
    @Override
    public Integer addAdmin(Admin admin) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert into users(admin_id, password) values (?,?)");
        ps.setString(1,admin.getAdminId());
        ps.setString(2,admin.getPassWord());
        int i = ps.executeUpdate();
        return i;
    }

    //2.1、查询老人信息
    @Override
    public List<Older> findAll() throws SQLException {
        ArrayList<Older> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        //查老人表信息
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM older");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Older older = new Older();
            older.setOlderId(rs.getInt("older_id"));
            older.setOlderName(rs.getString("older_name"));
            older.setOlderGender(rs.getString("older_gender"));
            older.setOlderPhone(rs.getLong("older_phone"));
            older.setOlderAddress(rs.getString("older_address"));
            older.setHealth(rs.getInt("health"));
            older.setCheckinDate(rs.getDate("checkin_date"));
            older.setRoomId(rs.getInt("room_id"));
            older.setNurseId(rs.getInt("nurse_id"));
            list.add(older);
        }
        return list;
    }

    //2.2、修改老人信息
    @Override
    public Integer updateOlder(Older older) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update older set older_name=?,older_gender=?,older_phone=?,older_address=?,health=?,\n" +
                "checkin_date=?,room_id=?,nurse_id=? WHERE older_id=?");
        ps.setString(1,older.getOlderName());
        ps.setString(2,older.getOlderGender());
        ps.setLong(3,older.getOlderPhone());
        ps.setString(4,older.getOlderAddress());
        ps.setInt(5,older.getHealth());
        ps.setDate(6,new Date(older.getCheckinDate().getTime()));
        ps.setInt(7,older.getRoomId());
        ps.setInt(8,older.getNurseId());
        ps.setInt(9,older.getOlderId());
        int i=ps.executeUpdate();
        return i;
    }

    @Override
    public List<Room> ShowRoom() throws SQLException {
        return null;
    }


    //展示事故信息
    public List<Trouble> findAllTrouble() throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM trouble");
        ResultSet rs = ps.executeQuery();
        ArrayList<Trouble> list = new ArrayList<>();
        while (rs.next()){
            Trouble tb = new Trouble();
            tb.setTroubleId(rs.getInt("trouble_id"));
            tb.setTroubleDate(rs.getDate("trouble_date"));
            tb.setTroubleMessage(rs.getString("trouble_message"));
            tb.setTroubleStatus(rs.getInt("trouble_status"));
            list.add(tb);
        }
        return list;
    }
    //根据老人ID查询老人信息
    @Override
    public Older findOlderIdByOlder(Integer olderId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from older where older_id=?");
        ps.setInt(1,olderId);
        ResultSet rs = ps.executeQuery();
        Older older = new Older();
        while (rs.next()){
            older.setOlderId(rs.getInt("older_id"));
            older.setOlderName(rs.getString("older_name"));
            older.setOlderGender(rs.getString("older_gender"));
            older.setOlderPhone(rs.getLong("older_phone"));
            older.setOlderAddress(rs.getString("older_address"));
            older.setHealth(rs.getInt("health"));
            older.setCheckinDate(rs.getDate("checkin_date"));
            older.setRoomId(rs.getInt("room_id"));
            older.setNurseId(rs.getInt("nurse_id"));
        }
        return older;
    }

    //展示房间的信息
    public List<Room> showRoom() throws SQLException {
        ArrayList<Room> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from room");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Room room = new Room();
            room.setId(rs.getInt("id"));
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomType(rs.getString("room_type"));
            room.setRoomStatus(rs.getInt("room_status"));
            list.add(room);
        }
        return list;
    }
    //展示可用的房间类型的信息
    @Override
    public List<Room> showRoomByStatus() throws SQLException {
        ArrayList<Room> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from room where room_status<2");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Room room = new Room();
            room.setId(rs.getInt("id"));
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomType(rs.getString("room_type"));
            room.setRoomStatus(rs.getInt("room_status"));
            list.add(room);
        }
        return list;
    }
    //修改事故信息
    public Trouble findTroubleById(Integer troubleId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from trouble where trouble_id=?");
        ps.setInt(1,troubleId);
        ResultSet rs = ps.executeQuery();
        Trouble tb = new Trouble();
        while (rs.next()){
            tb.setTroubleId(rs.getInt("trouble_id"));
            tb.setTroubleDate(rs.getDate("trouble_date"));
            tb.setTroubleMessage(rs.getString("trouble_message"));
            tb.setTroubleStatus(rs.getInt("trouble_status"));
        }
        return tb;
    }
    public Integer updateTroubleById(Trouble trouble) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("UPDATE trouble SET trouble_date=?,trouble_message=?,trouble_status=? where trouble_id=?");
        ps.setDate(1, new Date(trouble.getTroubleDate().getTime()));
        ps.setString(2,trouble.getTroubleMessage());
        ps.setInt(3,trouble.getTroubleStatus());
        ps.setInt(4,trouble.getTroubleId());
        int i=ps.executeUpdate();
        return i;
    }
    //新增事故信息
    public Integer addTrouble(Trouble trouble) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("INSERT INTO trouble(trouble_date,trouble_message,trouble_status)VALUES(?,?,?)");
        ps.setDate(1,new Date(trouble.getTroubleDate().getTime()));
        ps.setString(2,trouble.getTroubleMessage());
        ps.setInt(3,trouble.getTroubleStatus());
        int i=ps.executeUpdate();
        return i;
    }
    //删除事故信息
    public Integer deleteTrouble(Integer troubleId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from trouble where trouble_id=?");
        ps.setInt(1,troubleId);
        int i=ps.executeUpdate();
        return i;
    }
    //展示房间类型的信息
    @Override
    public List<Room> showRoomByType(String typeName) throws SQLException {
        ArrayList<Room> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from room where room_type=? and room_status<2");
        ps.setString(1,typeName);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Room room = new Room();
            room.setId(rs.getInt("id"));
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomType(rs.getString("room_type"));
            room.setRoomStatus(rs.getInt("room_status"));
            list.add(room);
        }
        return list;
    }

    //对房间信息进行修改
    @Override
    public Integer updateRoomMessage(Room room) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update room set room_id=?,room_status=? where id=?");
        ps.setInt(1,room.getRoomId());
        ps.setInt(2,room.getRoomStatus());
        ps.setInt(3,room.getId());
        int i = ps.executeUpdate();
        return i;
    }

    @Override
    public Integer deleteRoomMessage(Integer id) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from room where room.id=?");
        ps.setInt(1,id);
        int i = ps.executeUpdate();
        return i;
    }

    //添加一个房间信息
    public Integer addRoomMessage(Room room) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert into room (room_id, room_type,room_status)values (?,?,?)");
        ps.setInt(1,room.getRoomId());
        ps.setString(2,room.getRoomType());
        ps.setInt(3,room.getRoomStatus());
        int i = ps.executeUpdate();
        return i;
    }
    //展示入住费用信息
    @Override
    public List<PayMent> findAllPayMent() throws SQLException {
        ArrayList<PayMent> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from payment");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            PayMent payMent = new PayMent();
            payMent.setCheckinId(rs.getInt("checkin_id"));
            payMent.setOlderId(rs.getInt("older_id"));
            payMent.setCheckinFee(rs.getInt("checkin_fee"));
            payMent.setPayStatus(rs.getInt("pay_status"));
            payMent.setStareDate(rs.getDate("start_date"));
            payMent.setEndDate(rs.getDate("end_date"));
            list.add(payMent);
        }
        return list;
    }
    //展示所有护理人员信息
    @Override
    public List<Nurse> findAllNurse() throws SQLException {
        ArrayList<Nurse> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from nurse");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Nurse nurse = new Nurse();
            nurse.setNurseId(rs.getInt("nurse_id"));
            nurse.setNurseName(rs.getString("nurse_name"));
            nurse.setNurseGender(rs.getString("nurse_gender"));
            nurse.setNurseSalary(rs.getDouble("salary"));
            nurse.setGrade(rs.getInt("grade"));
            list.add(nurse);
        }
        return list;
    }

    //判断护理人是否存在
    @Override
    public Nurse existsNurseId(Integer integer) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from nurse where nurse_id=?");
        ps.setInt(1,integer);
        ResultSet rs = ps.executeQuery();
        Nurse nurse = null;
        while (rs.next()){
            nurse = new Nurse();
            nurse.setNurseName(rs.getString("nurse_name"));
            nurse.setNurseGender(rs.getString("nurse_gender"));
            nurse.setNurseSalary(rs.getDouble("salary"));
            nurse.setGrade(rs.getInt("grade"));
            nurse.setNurseId(rs.getInt("nurse_id"));
        }
        return nurse;
    }

    //修改护理人员信息
    @Override
    public Integer updateNurse(Nurse nurse) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update nurse set nurse_name=?,nurse_gender=?,salary=?,grade=? where nurse_id=?");
        ps.setString(1,nurse.getNurseName());
        ps.setString(2,nurse.getNurseGender());
        ps.setDouble(3,nurse.getNurseSalary());
        ps.setInt(4,nurse.getGrade());
        ps.setInt(5,nurse.getNurseId());
        Integer i = ps.executeUpdate();
        return i;
    }

    //添加一名护理人
    @Override
    public Integer addNurse(Nurse nurse) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert  into nurse (nurse_name,nurse_gender,salary,grade) values (?,?,?,?)");
        ps.setString(1,nurse.getNurseName());
        ps.setString(2,nurse.getNurseGender());
        ps.setDouble(3,nurse.getNurseSalary());
        ps.setInt(4,nurse.getGrade());
        int i = ps.executeUpdate();
        return i;
    }

    //删除一名护理人员信息
    @Override
    public Integer deleteNurse(Integer integer) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from nurse where nurse_id=?");
        ps.setInt(1,integer);
        int i = ps.executeUpdate();
        return i;
    }

    //添加老人
    @Override
    public Integer addOlder(Older older) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert into older(older_name, older_gender, older_phone, older_address, health, checkin_date, room_id, nurse_id) values (?,?,?,?,?,?,?,?)");
        ps.setString(1,older.getOlderName());
        ps.setString(2,older.getOlderGender());
        ps.setLong(3,older.getOlderPhone());
        ps.setString(4,older.getOlderAddress());
        ps.setInt(5,older.getHealth());
        ps.setDate(6,new Date(older.getCheckinDate().getTime()));
        ps.setInt(7,older.getRoomId());
        ps.setInt(8,older.getNurseId());
        int i = ps.executeUpdate();
        return i;
    }

    //删除老人
    @Override
    public Integer deleteOlder(Integer deleteOlderId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from older where older_id=?");
        ps.setInt(1,deleteOlderId);
        int i = ps.executeUpdate();
        return i;
    }
    //展示所有老人的健康信息
    public List<Health> showHealthByType() throws SQLException {
        ArrayList<Health> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from health");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Health health = new Health();
            health.setOlderId(rs.getInt("older_id"));
            health.setHealth(rs.getInt("health"));
            health.setDisease(rs.getString("disease"));
            health.setMedicineId(rs.getInt("medicine_id"));
            list.add(health);
        }
        return list;
    }
    //修改老人的健康信息
    @Override
    public Integer updateHealthMessage(Health health) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update health set health=?,disease=?,medicine_id=? where older_id=?");
        ps.setInt(1,health.getHealth());
        ps.setString(2,health.getDisease());
        ps.setDouble(3,health.getMedicineId());
        ps.setInt(4,health.getOlderId());
        int i = ps.executeUpdate();
        return i;
    }
    //查看药品的信息
    public List<Medicine> showMedicine() throws SQLException {
        ArrayList<Medicine> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from medicine");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Medicine medicine = new Medicine();
            medicine.setMedicineId(rs.getInt("medicine_id"));
            medicine.setMedicineName(rs.getString("medicine_name"));
            medicine.setMedicinePrice(rs.getInt("medicine_price"));
            list.add(medicine);
        }
        return list;
    }
    //新增一个药品信息
    public Integer addMedicine(Medicine medicine) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert into medicine( medicine_name, medicine_price) values (?,?)");
        ps.setString(1,medicine.getMedicineName());
        ps.setInt(2,medicine.getMedicinePrice());
        Integer i = ps.executeUpdate();
        return i;
    }
    //修改一个药品的信息
    public Integer updateMedicine(Medicine medicine) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update medicine set medicine_name=?,medicine_price=? where medicine_id=?");
        ps.setString(1,medicine.getMedicineName());
        ps.setInt(2,medicine.getMedicinePrice());
        ps.setInt(3,medicine.getMedicineId());
        Integer i = ps.executeUpdate();
        return i;
    }
    //删除一个药品的信息
    public Integer deleteMedicine(Integer integer) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from medicine where medicine_id=?");
        ps.setInt(1,integer);
        Integer i = ps.executeUpdate();
        return i;
    }
    //添加一个健康信息
    @Override
    public Integer addHealth(Health health) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert into health (older_id, health, disease, medicine_id)values (?,?,?,?)");
        ps.setInt(1,health.getOlderId());
        ps.setInt(2,health.getHealth());
        ps.setString(3,health.getDisease());
        ps.setInt(4,health.getMedicineId());
        Integer i = ps.executeUpdate();
        return i;
    }

    //展示护理信息
    public List<NursingPayMent> showAllNursing() throws SQLException {
        ArrayList<NursingPayMent> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from nursing_payment");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            NursingPayMent ment = new NursingPayMent();
            ment.setNursingId(rs.getInt("nursing_pay_id"));
            ment.setNursingGrade(rs.getInt("nursing_grade"));
            ment.setNursingPay(rs.getInt("nursing_pay"));
            ment.setOlderId(rs.getInt("older_id"));
            list.add(ment);
        }
        return list;
    }
    //展示缴费部分信息
    @Override
    public List<PayMent> findPayMentByOlderId(Integer integer) throws SQLException {
        ArrayList<PayMent> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("SELECT p.older_id,p.checkin_fee,p.start_date,p.end_date FROM payment p WHERE older_id=?");
        ps.setInt(1,integer);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            PayMent payMent = new PayMent();
            payMent.setOlderId(rs.getInt("older_id"));
            payMent.setCheckinFee(rs.getInt("checkin_fee"));
            payMent.setStareDate(rs.getDate("start_date"));
            payMent.setEndDate(rs.getDate("end_date"));
            list.add(payMent);
        }
        return list;
    }
    //展示访客信息
    public List<Visitor> showAllVisitor() throws SQLException {
        ArrayList<Visitor> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from visitor");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Visitor vs = new Visitor();
            vs.setVisitorId(rs.getInt("visitor_id"));
            vs.setVisitorName(rs.getString("visitor_name"));
            vs.setVisitorDate(rs.getDate("visitor_date"));
            vs.setVisitorPhone(rs.getLong("visitor_phone"));
            vs.setOlderId(rs.getInt("older_id"));
            list.add(vs);
        }
        return list;
    }
    //根据ID查询访客
    public Visitor findVisitorById(Integer vistorId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from visitor where visitor_id=?");
        ps.setInt(1,vistorId);
        ResultSet rs = ps.executeQuery();
        Visitor visitor = new Visitor();
        while (rs.next()){
            visitor.setVisitorId(rs.getInt("visitor_id"));
            visitor.setVisitorName(rs.getString("visitor_name"));
            visitor.setVisitorDate(rs.getDate("visitor_date"));
            visitor.setVisitorPhone(rs.getLong("visitor_phone"));
            visitor.setOlderId(rs.getInt("older_id"));
        }
        return visitor;
    }
    //修改访客信息
    public Integer updateVisitore(Visitor visitor) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update visitor set visitor_name=?,visitor_date=?,visitor_phone=? where visitor_id=?");
        ps.setString(1,visitor.getVisitorName());
        ps.setDate(2,new Date(visitor.getVisitorDate().getTime()));
        ps.setLong(3,visitor.getVisitorPhone());
        ps.setInt(4,visitor.getVisitorId());
        int i = ps.executeUpdate();
        return i;
    }
    //新增访客信息
    public Integer addVisitor(Visitor visitor) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("INSERT INTO visitor(visitor_name,visitor_date,visitor_phone,older_id)VALUES(?,?,?,?)");
        ps.setString(1, visitor.getVisitorName());
        ps.setDate(2,new Date(visitor.getVisitorDate().getTime()));
        ps.setLong(3,visitor.getVisitorPhone());
        ps.setInt(4,visitor.getOlderId());
        int i=ps.executeUpdate();
        return i;
    }
    //删除访客信息
    public Integer deleteVisitor(Integer visitorId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from visitor where visitor_id=?");
        ps.setInt(1,visitorId);
        int i = ps.executeUpdate();
        return i;
    }

    //查询老人缴费状态
    @Override
    public List<PayMent> findOlderByStatus(Integer olderId) throws SQLException {
        ArrayList<PayMent> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from payment where older_id=?");
        ps.setInt(1,olderId);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            PayMent payMent = new PayMent();
            payMent.setPayStatus(rs.getInt("pay_status"));
            payMent.setOlderId(rs.getInt("older_id"));
            list.add(payMent);
        }
        return list;
    }


    //查看外出信息
    @Override
    public List<Outing> showAllOuting() throws SQLException {
        ArrayList<Outing> list = new ArrayList<>();
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from outing");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Outing outing = new Outing();
            outing.setOutingId(rs.getInt("outing_id"));
            outing.setOutingReason(rs.getString("outing_reason"));
            outing.setOlderId(rs.getInt("older_id"));
            outing.setOutingDate(rs.getDate("outing_date"));
            outing.setOutingStatus(rs.getInt("outing_status"));
            list.add(outing);
        }
        return list;
    }

    //外出报备
    @Override
    public Integer addOuting(Outing outing) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("INSERT INTO outing(outing_reason,older_id,outing_date,outing_status)VALUES(?,?,?,?)");
        ps.setString(1,outing.getOutingReason());
        ps.setInt(2,outing.getOlderId());
        ps.setDate(3,new Date(outing.getOutingDate().getTime()));
        ps.setInt(4,outing.getOutingStatus());
        int i=ps.executeUpdate();
        return i;
    }

    //判断外出报备ID是否存在
    @Override
    public Outing existsOutingId(Integer outingId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from outing where outing_id=?");
        ps.setInt(1,outingId);
        ResultSet rs = ps.executeQuery();
        Outing outing = null;
        while (rs.next()){
            outing = new Outing();
            outing.setOutingId(rs.getInt("outing_id"));
            outing.setOutingReason(rs.getString("outing_reason"));
            outing.setOlderId(rs.getInt("older_id"));
            outing.setOutingDate(rs.getDate("outing_date"));
            outing.setOutingStatus(rs.getInt("outing_status"));
        }
        return outing;
    }

    //外出归来修改报备状态
    @Override
    public Integer updateOuting(Outing outing) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("update outing set outing_status=? where outing_id=?");
        ps.setInt(1,outing.getOutingStatus());
        ps.setInt(2,outing.getOutingId());
        int i = ps.executeUpdate();
        return i;
    }
    //分页展示访客信息及老人信息及房间号
    public List<OlderAndVisitorAndRoom> OlderAndVisitorAndRoomByPage(Integer index) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select v.*,o.older_name,r.room_status,r.room_type FROM older o left join visitor v ON v.older_id=o.older_id LEFT JOIN room r ON o.room_id=r.room_id LIMIT ?,2");
        ps.setInt(1,index);
        ArrayList<OlderAndVisitorAndRoom> list = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Older older =new Older();
            older.setOlderId(rs.getInt("older_id"));
            older.setOlderName(rs.getString("older_name"));
            /*older.setOlderGender(rs.getString("older_gender"));
            older.setOlderPhone(rs.getLong("older_phone"));
            older.setOlderAddress(rs.getString("older_address"));
            older.setHealth(rs.getInt("health"));
            older.setCheckinDate(rs.getDate("checkin_date"));*/
            /*older.setRoomId(rs.getInt("room_id"));
            older.setNurseId(rs.getInt("nurse_id"));*/
            Visitor visitor = new Visitor();
            visitor.setVisitorId(rs.getInt("visitor_id"));
            visitor.setVisitorName(rs.getString("visitor_name"));
            visitor.setVisitorDate(rs.getDate("visitor_date"));
            visitor.setVisitorPhone(rs.getLong("visitor_phone"));
            visitor.setOlderId(rs.getInt("older_id"));
            Room room = new Room();
            //room.setId(rs.getInt("id"));
            //room.setRoomId(rs.getInt("room_id"));
            room.setRoomType(rs.getString("room_type"));
            room.setRoomStatus(rs.getInt("room_status"));
            OlderAndVisitorAndRoom olderAndVisitorAndRoom = new OlderAndVisitorAndRoom(older,visitor,room);
            list.add(olderAndVisitorAndRoom);
        }
        return list;
    }
    public Integer findOlderNum() throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select COUNT(*) AS nums FROM older o left join visitor v ON v.older_id=o.older_id LEFT JOIN room r ON o.room_id=r.room_id;");
        ResultSet rs = ps.executeQuery();
        Integer nums=null;
        while (rs.next()){
            nums= rs.getInt("nums");
        }
        return nums;
    }
}
